package nxu.it.dao;

import nxu.it.model.Todo;
import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.customizer.BindBean;
import org.jdbi.v3.sqlobject.statement.SqlQuery;
import org.jdbi.v3.sqlobject.statement.SqlUpdate;

import java.util.List;
import java.util.Optional;

public interface TodoDao {
    @SqlQuery("SELECT t.id,t.content,(select categoryname from category where id=(t.category+0) LIMIT 1) as contenttype,t.complete,t.create_at,t.update_at FROM todo as t")
    @RegisterBeanMapper(Todo.class)
    List<Todo> list();

    @SqlQuery("SELECT t.id,t.content,(select categoryname from category where id=(t.category+0) LIMIT 1) as contenttype,t.complete,t.create_at,t.update_at FROM todo as t where  t.username = :username")
    @RegisterBeanMapper(Todo.class)
    List<Todo> listByUser(@Bind String username);

    @SqlQuery("SELECT  count(1) pagetotal FROM todo as t where  t.username = :username")
    @RegisterBeanMapper(Todo.class)
    int listByUserCount(@Bind String username);

    @SqlQuery("SELECT t.id,t.content,(select categoryname from category where id=(t.category+0) LIMIT 1) as contenttype,t.complete,t.create_at,t.update_at FROM todo as t WHERE t.id = :id")
    @RegisterBeanMapper(Todo.class)
    Optional<Todo> get(@Bind("id") int id);

    @SqlQuery("SELECT t.id,t.content,(select categoryname from category where id=(t.category+0) LIMIT 1) as contenttype,t.complete,t.create_at,t.update_at FROM todo as t WHERE t.id = :id AND t.username = :username")
    @RegisterBeanMapper(Todo.class)
    Optional<Todo> getByUser(@Bind("id") int id,@Bind("username") String username);

    @SqlUpdate("INSERT INTO todo (content,category,complete,create_at,update_at,username) VALUES (:content,:contenttype,:complete,:createAt,:updateAt,:username)")
    void save(@BindBean Todo todo);

    @SqlUpdate("DELETE FROM todo WHERE id = :id AND username = :username")
    void deleteByUser(@Bind("id") int id,@Bind("username") String username);

    @SqlUpdate("UPDATE todo SET content = :content, category = :contenttype, complete = :complete, update_at = :updateAt WHERE id = :id AND username = :username")
    void updateByUser(@BindBean Todo todo,@Bind("username") String username);

    @SqlUpdate("UPDATE todo SET complete = !complete WHERE id = :id AND username = :username")
    void checkByUser(@Bind("id") int id,@Bind("username") String username);
}
